#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import pymysql
import os
from dotenv import load_dotenv

load_dotenv()

def check_database_connection():
    """检查数据库连接和表结构"""
    try:
        # 数据库连接参数
        connection = pymysql.connect(
            host='localhost',
            port=3306,
            user='flask_dev',
            password='dev123456',
            database='flask_local',
            charset='utf8mb4'
        )
        
        print("数据库连接成功！")
        
        with connection.cursor() as cursor:
            # 检查数据库中的表
            cursor.execute("SHOW TABLES")
            tables = cursor.fetchall()
            print(f"\n数据库中的表 ({len(tables)}个):")
            for table in tables:
                print(f"  - {table[0]}")
            
            # 检查用户表数据
            print("\n用户表数据:")
            cursor.execute("SELECT COUNT(*) FROM user")
            user_count = cursor.fetchone()[0]
            print(f"  用户总数: {user_count}")
            
            if user_count > 0:
                cursor.execute("SELECT id, real_name, role, phone FROM user LIMIT 5")
                users = cursor.fetchall()
                print("  前5个用户:")
                for user in users:
                    role_name = {0: '管理员', 1: '医生', 2: '患者'}.get(user[2], '未知')
                    print(f"    ID:{user[0]} 姓名:{user[1]} 角色:{role_name} 电话:{user[3]}")
            
            # 检查评估记录表数据
            print("\n评估记录表数据:")
            cursor.execute("SELECT COUNT(*) FROM scale_assessment")
            assessment_count = cursor.fetchone()[0]
            print(f"  评估记录总数: {assessment_count}")
            
            if assessment_count > 0:
                cursor.execute("""
                    SELECT sa.id, sa.user_id, sa.assessor_id, sa.assessment_date, 
                           sa.hamd_total, sa.hama_total, sa.ymrs_total,
                           u1.real_name as patient_name, u2.real_name as doctor_name
                    FROM scale_assessment sa
                    LEFT JOIN user u1 ON sa.user_id = u1.id
                    LEFT JOIN user u2 ON sa.assessor_id = u2.id
                    ORDER BY sa.assessment_date DESC
                    LIMIT 5
                """)
                assessments = cursor.fetchall()
                print("  最近5条评估记录:")
                for assessment in assessments:
                    print(f"    ID:{assessment[0]} 患者:{assessment[7]} 医生:{assessment[8]} 日期:{assessment[3]} HAMD:{assessment[4]} HAMA:{assessment[5]} YMRS:{assessment[6]}")
            
            # 检查医生患者绑定关系
            print("\n医生患者绑定关系:")
            cursor.execute("SELECT COUNT(*) FROM doctor_patient_bind WHERE status = 1")
            bind_count = cursor.fetchone()[0]
            print(f"  有效绑定关系数: {bind_count}")
            
            if bind_count > 0:
                cursor.execute("""
                    SELECT dpb.id, dpb.doctor_id, dpb.patient_id, dpb.bind_time,
                           u1.real_name as doctor_name, u2.real_name as patient_name
                    FROM doctor_patient_bind dpb
                    LEFT JOIN user u1 ON dpb.doctor_id = u1.id
                    LEFT JOIN user u2 ON dpb.patient_id = u2.id
                    WHERE dpb.status = 1
                    ORDER BY dpb.bind_time DESC
                    LIMIT 5
                """)
                binds = cursor.fetchall()
                print("  最近5条绑定关系:")
                for bind in binds:
                    print(f"    ID:{bind[0]} 医生:{bind[4]} 患者:{bind[5]} 绑定时间:{bind[3]}")
            
            # 检查量表详细数据
            print("\n量表详细数据:")
            cursor.execute("SELECT COUNT(*) FROM scale_hamd17")
            hamd_count = cursor.fetchone()[0]
            cursor.execute("SELECT COUNT(*) FROM scale_hama")
            hama_count = cursor.fetchone()[0]
            cursor.execute("SELECT COUNT(*) FROM scale_ymrs")
            ymrs_count = cursor.fetchone()[0]
            print(f"  HAMD17记录: {hamd_count}")
            print(f"  HAMA记录: {hama_count}")
            print(f"  YMRS记录: {ymrs_count}")
            
    except Exception as e:
        print(f"数据库连接失败: {e}")
        return False
    
    finally:
        if 'connection' in locals():
            connection.close()
    
    return True

if __name__ == "__main__":
    print("开始检查数据库...")
    check_database_connection()
